﻿using Artech.Common;
using Artech.Core.Caching;
using Artech.Core.Globalization;
using Artech.Core.Resources.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.Unity.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Transactions;

namespace Artech.Core.Resources
{
    [ConfigurationElementType(typeof(DbResourceProviderData))]
    public class DbResourceProvider : ResourceProviderBase, IResourceProvider, IDbProvider
    {
        private const string Procedure4GetByName = "artech_StringResources_GetByName";
        private const string Procedure4GetByCategory = "artech_StringResources_GetByCategory";
        private const string Procedure4GetAll = "artech_StringResources_GetAll";

        private const string Procedure4GetResource4Edit = "artech_StringResources_GetResources4Edit";
        private const string Procedure4AddResource = "artech_StringResources_Add";
        private const string Procedure4UpdateResource = "artech_StringResources_Update";
        private const string Procedure4DeleteResource = "artech_StringResources_Delete";

        public IDataAccess DataAccess { get; private set; }
        public DbResourceProvider(string databaseName, string applicationName, bool cacheEnabled, CacheGranularity cacheMode, Func<ICacheStore<IEnumerable<StringResourceItem>>> cacheStoreAccessor)
            : base(cacheEnabled, cacheMode, cacheStoreAccessor)
        {
            Guard.ArgumentNotNullOrEmpty(databaseName, "databaseName");
            Guard.ArgumentNotNullOrEmpty(applicationName, "applicationName");
            this.DataAccess = DataAccessBuilder.Current.CreateDataAccess(databaseName, applicationName);
        }

        public override StatefulEntryCollection<StringResourceEntry> GetStringsForEdit(StringResourceSearchCriteria criteria, int pageSize, int pageIndex, ResourceEntryField orderBy, SortDirection sortDirection)
        {
            Guard.ArgumentNotNull(criteria, "criteria");
            Guard.ArgumentNotNull(orderBy, "orderBy");

            List<StringResourceEntry> entries = new List<StringResourceEntry>();
            DbCommand command = this.DataAccess.Database.GetStoredProcCommand(Procedure4GetResource4Edit);
            string cultures = string.Join(",", criteria.Cultures.Select(culture=>culture.Name).ToArray());
            if (!string.IsNullOrEmpty(cultures))
            {
                cultures = "," + cultures + ",";
            }
            using (var reader = this.DataAccess.Database.ExecuteReader(Procedure4GetResource4Edit,
                this.DataAccess.ApplicationName.ToDbParameterValue(),
                criteria.Category.ToDbParameterValue(),
                criteria.Name.ToDbParameterValue(),
                cultures.ToDbParameterValue(),
                orderBy.Value.ToDbParameterValue(),
                sortDirection == SortDirection.Ascending,
                pageSize,
                pageIndex))
            {
                while (reader.Read())
                {
                    CultureInfo culture;
                    string language = reader.GetString(4);
                    string region = reader.GetValue(5).ToString();
                    if (string.IsNullOrEmpty(region.ToString()))
                    {
                        culture = new CultureInfo(language);
                    }
                    else
                    {
                        culture = new CultureInfo(string.Format("{0}-{1}", language, region));
                    }
                    StringResourceEntry entry = StringResourceEntry.NewResoureEntry(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), culture);
                    entry.AuditInfo.CreatedBy = reader.GetString(6);
                    entry.AuditInfo.CreatedTime = reader.GetDateTime(7).ConvertAfterReading();
                    entry.AuditInfo.LastUpdatedBy = reader.GetString(8);
                    entry.AuditInfo.LastUpdatedTime = reader.GetDateTime(9).ConvertAfterReading();
                    entry.AuditInfo.VersionNo = reader.GetInt32(10);
                    entry.AuditInfo.TransactionId = reader.GetString(11);
                    entries.Add(entry);
                }
            }

            return new StatefulEntryCollection<StringResourceEntry>(entries);
        }
         public override void UpdateStrings(StatefulEntryCollection<StringResourceEntry> resourceEntries)
        {
            Guard.ArgumentNotNull(resourceEntries, "resourceEntries");

            IEnumerable<StringResourceEntry> addedEntries = resourceEntries.GetAddedEntries();
            IEnumerable<StringResourceEntry> modifiedEntries = resourceEntries.GetModifiedEntries();
            IEnumerable<StringResourceEntry> deletedEntries = resourceEntries.GetDeletedEntries();

            this.DataAccess.ExecuteTransactionalOperations(
                transaction =>
                    {
                        DeleteResourceEntries(deletedEntries, transaction);
                        UpdateResourceEntries(modifiedEntries, transaction);
                        AddResourceEntries(addedEntries, transaction);
                    }, 
                () =>
                    {
                        DeleteResourceEntries(deletedEntries);
                        UpdateResourceEntries(modifiedEntries);
                        AddResourceEntries(addedEntries);
                    });
        }

        private void DeleteResourceEntries(IEnumerable<StringResourceEntry> deletedEntries, DbTransaction tranasaction = null)
        {
            foreach (var entry in deletedEntries)
            {
                if (null != tranasaction)
                {
                    this.DataAccess.Database.ExecuteNonQuery(tranasaction, Procedure4DeleteResource,
                        this.DataAccess.ApplicationName,
                        entry.Id,
                        ApplicationContext.Current.UserName,
                        DateTimeConverter.Now,
                        entry.AuditInfo.VersionNo,
                        ApplicationContext.Current.TransactionId);
                }
                else
                {
                    this.DataAccess.Database.ExecuteNonQuery(Procedure4DeleteResource,
                        this.DataAccess.ApplicationName,
                        entry.Id,
                        ApplicationContext.Current.UserName,
                        DateTimeConverter.Now,
                        entry.AuditInfo.VersionNo,
                        ApplicationContext.Current.TransactionId);
                }
            }
        }
        private void UpdateResourceEntries(IEnumerable<StringResourceEntry> modifiedEntries, DbTransaction tranasaction = null)
        {
            foreach (var entry in modifiedEntries)
            {
                if (null != tranasaction)
                {
                    this.DataAccess.Database.ExecuteNonQuery(tranasaction, Procedure4UpdateResource,
                        this.DataAccess.ApplicationName,
                        entry.Id,
                        entry.Category,
                        entry.Name,
                        entry.Value,
                        entry.Culture.GetLanguage(),
                        entry.Culture.GetRegion().ToDbParameterValue(),
                        ApplicationContext.Current.UserName,
                        DateTimeConverter.Now,
                        entry.AuditInfo.VersionNo,
                        ApplicationContext.Current.TransactionId);
                }
                else
                {
                    this.DataAccess.Database.ExecuteNonQuery(Procedure4UpdateResource,
                        this.DataAccess.ApplicationName,
                        entry.Id,
                        entry.Category,
                        entry.Name,
                        entry.Value,
                        entry.Culture.GetLanguage(),
                        entry.Culture.GetRegion().ToDbParameterValue(),
                        ApplicationContext.Current.UserName,
                        DateTimeConverter.Now,
                        entry.AuditInfo.VersionNo,
                        ApplicationContext.Current.TransactionId);
                }
            }
        }
        private void AddResourceEntries(IEnumerable<StringResourceEntry> addedEntries, DbTransaction tranasaction = null)
        {
            foreach (var entry in addedEntries)
            {

                if (null != tranasaction)
                {
                    this.DataAccess.Database.ExecuteNonQuery(tranasaction, Procedure4AddResource,
                        this.DataAccess.ApplicationName,
                        entry.Id,
                        entry.Category,
                        entry.Name,
                        entry.Value,
                        entry.Culture.GetLanguage(),
                        entry.Culture.GetRegion().ToDbParameterValue(),
                        ApplicationContext.Current.UserName,
                        DateTimeConverter.Now,
                        ApplicationContext.Current.TransactionId);
                }
                else
                {
                    this.DataAccess.Database.ExecuteNonQuery(Procedure4AddResource,
                        this.DataAccess.ApplicationName,
                        entry.Id,
                        entry.Category,
                        entry.Name,
                        entry.Value,
                        entry.Culture.GetLanguage(),
                        entry.Culture.GetRegion().ToDbParameterValue(),
                        ApplicationContext.Current.UserName,
                        DateTimeConverter.Now,
                        ApplicationContext.Current.TransactionId);
                }
            }
        }

         protected override IEnumerable<StringResourceItem> GetStringsCore(CultureInfo culture)
        {
            return this.GetStringsByExecuteProcedure(Procedure4GetByName, this.DataAccess.ApplicationName, culture.GetLanguage(), culture.GetRegion());
        }

         protected override IEnumerable<StringResourceItem> GetStringsCore(string category, CultureInfo culture)
        {
            return this.GetStringsByExecuteProcedure(Procedure4GetByName, this.DataAccess.ApplicationName, category, culture.GetLanguage(), culture.GetRegion());
        }

         protected override IEnumerable<StringResourceItem> GetStringsCore(string category, string name, CultureInfo culture)
        {
            return this.GetStringsByExecuteProcedure(Procedure4GetByName, this.DataAccess.ApplicationName, category, name, culture.GetLanguage(), culture.GetRegion());
        }

        private IEnumerable<StringResourceItem> GetStringsByExecuteProcedure(string procedureName, params object[] parameterValues)
        {
            using (IDataReader reader = this.DataAccess.ExecuteReader(procedureName, parameterValues))
            {
                while (reader.Read())
                {
                    string category = reader.GetString(0);
                    string name = reader.GetString(1);
                    string value = reader.GetString(2);
                    string language = reader.GetString(3);
                    string region = reader.GetValue(4).ToString();
                    yield return new StringResourceItem(category, name, value, language, region);
                }
            }
        }
    }
}